--USE scm_main;
-- 需要先创建拆分字符串存储过程（split_str）
if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_saveRepair')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_saveRepair
GO
CREATE PROCEDURE bd_proc_saveRepair
    @orderNo AS varchar(10),
	@lx AS varchar(10),
	@glyNo AS VARCHAR(20),
	@orderId as numeric,
	@pcIp as VARCHAR(20),
	@repairStartDate as DATE,
	@repairEndDate as DATE,
	@remark as VARCHAR(max)
AS
BEGIN
  DECLARE @peopleNum int
	DECLARE @user_serial NUMERIC
	DECLARE @count int
	begin try
		begin tran

			--创建 返回不满足请假的人的原因
			create table #return_table(
				user_serial numeric not null,
				user_no VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				user_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				dept_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS not NULL
			)
				--创建可提交的人员列表
			select * into #qualified_people from bd_temp_checkPeople where 1=2;
			--定义游标
			--声明一个游标userOfLeave
			DECLARE userOfRepair CURSOR LOCAL FOR SELECT
				b.user_serial
			FROM
				bd_temp_checkPeople b
				INNER JOIN dt_user u on u.user_serial = b.user_serial
			WHERE order_id = @orderId

			OPEN userOfRepair FETCH NEXT FROM userOfRepair INTO @user_serial--提取第一条数据存放在变量中
			WHILE @@fetch_status = 0
				BEGIN
					select @count = count(1) from bd_kq_accounts a
					INNER JOIN (
					SELECT
						@user_serial as userSerial,*
					FROM
						( SELECT YEAR ( CONVERT ( DATE, start_date )) AS leaveyear, MONTH ( CONVERT ( DATE, start_date )) AS leavemonth FROM bd_temp_repairTime where order_id = @orderId ) b
					GROUP BY
						b.leaveyear,
						b.leavemonth) c on a.user_serial = c.userSerial and a.year = c. leaveyear and a.month = c.leavemonth
				if (@count>0)
					BEGIN
						--补出勤的日期中有已封账的日期
						insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
								SELECT
									u.user_serial,
									u.user_no,
									u.user_lname,
									d.dep_name,
									'部分补出勤时间包含在封账时段内'
								FROM
									dt_user u
									INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
									where u.user_serial = @user_serial
					END
					--不存在封账的日期
					ELSE BEGIN
					--插入到#qualified_people
						insert into #qualified_people
							select  * from bd_temp_checkPeople where user_serial = @user_serial and order_id = @orderId;
					END
					FETCH NEXT FROM userOfRepair INTO @user_serial
				END
			select @peopleNum = count(1) from #qualified_people;
			if (@peopleNum>0)
				BEGIN
					--保存补出勤单
					INSERT INTO bd_repair_attendance(
								order_id,
								order_no,
								start_date,
								end_date,
								creater_id,
								people_num,
								remark,
								pc_ip
						)VALUES(
								@orderId,
								@orderNo,
								@repairStartDate,
								@repairEndDate,
								@glyNo,
								@peopleNum,
								@remark,
								@pcIp
						);

					--插入上班时间
					INSERT into lr_jl
						(
						sj,
						user_serial,
						gly_no,
						order_id,
						pc_ip
						)
						SELECT
								r.start_date,
								b.user_serial,
								@glyNo,
								@orderId,
								@pcIp
							FROM
								#qualified_people b,
								bd_temp_repairTime r
							WHERE
								b.order_id = @orderId
								AND r.order_id = @orderId;

						INSERT into kt_jl
						(
						sj,
						user_serial,
						fx,
						gly_no,
						mode,
						order_id,
						whether_validity,
						pc_ip
						)
						SELECT
								r.start_date,
								b.user_serial,
								0,
								@glyNo,
								1,
								@orderId,
								0,
								@pcIp
							FROM
								#qualified_people b,
								bd_temp_repairTime r
							WHERE
								b.order_id = @orderId
								AND r.order_id = @orderId;

					IF ('' = @lx or null = @lx) BEGIN
					--插入下班时间
						INSERT into lr_jl
						(
						sj,
						user_serial,
						gly_no,
						order_id,
						pc_ip
						)
						SELECT
								r.end_date,
								b.user_serial,
								@glyNo,
								@orderId,
								@pcIp
							FROM
								#qualified_people b,
								bd_temp_repairTime r
							WHERE
								b.order_id = @orderId
								AND r.order_id = @orderId;
						INSERT into kt_jl
						(
						sj,
						user_serial,
						fx,
						gly_no,
						mode,
						order_id,
						whether_validity,
						pc_ip
						)
						SELECT
								r.end_date,
								b.user_serial,
								0,
								@glyNo,
								1,
								@orderId,
								0,
								@pcIp
							FROM
								#qualified_people b,
								bd_temp_repairTime r
							WHERE
								b.order_id = @orderId
								AND r.order_id = @orderId;
					END


					--计算考勤
					DECLARE @BeginDate DATE
								 ,@EndDate DATE
								 ,@users VARCHAR(MAX) = ''

					SELECT
						@BeginDate = start_date
					 ,@EndDate = end_date
					FROM bd_repair_attendance
					WHERE order_id = @orderId


					SELECT
						@users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
					FROM #qualified_people u
					WHERE u.order_id = @orderId

					SET @users = SUBSTRING(@users,0, LEN(@users))

					--  INSERT INTO temp_repair
					--  VALUES(@BeginDate, @EndDate, @users, @orderId, GETDATE())

					EXEC bd_proc_timecard_extend @BeginDate, @EndDate, @users, 1

					--  DECLARE @BeginDate DATE
					--         ,@EndDate DATE
					--         ,@users VARCHAR(MAX) = ''
					--
					--  SELECT
					--    @BeginDate = MIN(t.start_date)
					--   ,@EndDate = MAX(t.start_date)
					--  FROM bd_temp_repairTime t
					--      ,bd_temp_checkPeople u
					--  WHERE u.order_id = @orderId
					--
					--
					--  SELECT
					--    @users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
					--  FROM bd_temp_checkPeople u
					--  WHERE u.order_id = @orderId
					--
					--  SET @users = SUBSTRING(@users,0, LEN(@users))
					--
					--  EXEC bd_proc_timecard_extend @BeginDate, @EndDate, @users

					--  WHILE (@BeginDate <= @EndDate)
					--  BEGIN
					--    PRINT @BeginDate
					--
					--    SET @users=''
					--    SELECT
					--      @users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
					--    FROM bd_temp_repairTime t
					--        ,bd_temp_checkPeople u
					--    WHERE u.order_id = @orderId
					--    AND CONVERT(VARCHAR(10), t.start_date, 120) = @BeginDate
					--
					--    PRINT(SUBSTRING(@users,1,LEN(@users)-1))
					--
					--    EXEC bd_proc_timecard @BeginDate, @users
					--
					--    SET @BeginDate = CONVERT(VARCHAR(10), DATEADD(DAY, 1, @BeginDate), 120)
					--  END


				END

			delete from bd_temp_checkPeople where order_id = @orderId;
			delete from bd_temp_repairTime where order_id = @orderId
			--返回的数据
			SELECT
						user_no AS 'userNo',
						user_name AS 'userName',
						dept_name AS 'deptName',
						error_msg as 'errorMsg'
						from #return_table;

			DROP TABLE #return_table;
			DROP TABLE #qualified_people;
			commit tran
		end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
				CLOSE userOfRepair --关闭游标
				DEALLOCATE userOfRepair --释放游标
			END;
	end catch
END